/*
  @author : Taylor
  @desc   : 从临时表批量导入数据
*/
create or replace
procedure pro_data_batch_imp(
int_app_id in number,--应用ID
int_enterp_id in number,--企业ID
int_cate_id in number,--要添加到的分类ID
int_attrRef_cateId in number--属性关联的分类ID
) is
begin
  --从临时表读取所有数据
  declare cursor temp_data_cur is select * from APP_DATA_LOAD;
  content_row APP_DATA_LOAD%rowtype;
   int_content_id number;--存放内容表ID
   int_shop_id number;--存放门店表ID
   int_attr_value_id number;--存放属性值表ID
   var_system_date varchar2(100);--系统时间
   var_img_url varchar2(200);--缩略图
  begin
    open temp_data_cur;
    loop
      fetch temp_data_cur into content_row;
      exit when temp_data_cur%notfound;
      select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') into var_system_date from dual;
      --content表数据插入
      select CONTENT_TAB_SEQ.nextval into int_content_id from dual;
      var_img_url := '';
      if content_row.小图 is not null then
        var_img_url := 'WORKDIR/ENTERPRISE_1020/1060/CATELOG/DETAIL/' || content_row.小图;
      end if;
      Insert into CONTENT_TAB (INT_CONTENT_ID,VAR_CONTENT_NAME,INT_ENTERP_ID,INT_CATE_ID,INT_APP_ID,VAR_CONTENT_DESCRIPT,DAT_ADD_DATETIME,VAR_CONTENT_IMG_URL,INT_VIEW_COUNT,INT_ACTION,INT_DELETE_SIGN,DAT_UPDATE_DATETIME) 
      values (int_content_id,content_row.楼盘名称,int_enterp_id,int_cate_id,int_app_id,content_row.楼盘简介,var_system_date,var_img_url,0,null,0,var_system_date);
      
      --content_img_tab表数据插入
      splitStr2Array(content_row.相册,int_content_id);
      
      --enterp_shop_tab表数据插入
      if ((content_row.纬度 is not null) and (content_row.经度 is not null))
      then
         select ENTERP_SHOP_TAB_SEQ.nextval into int_shop_id from dual;
         Insert into ENTERP_SHOP_TAB (INT_SHOP_ID,INT_APP_ID,INT_ENTERP_ID,VAR_SHOP_NAME,VAR_SHOP_COUNTRY,VAR_SHOP_PROVINCE,VAR_SHOP_CITY,VAR_SHOP_LONGITUDE,VAR_SHOP_LATITUDE,VAR_SHOP_POSTNUM,VAR_SHOP_PHONENUM,VAR_SHOP_ADDRESS,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_CONTENT_ID) 
         values (int_shop_id,int_app_id,int_enterp_id,content_row.楼盘名称,'中国','四川',content_row.地区,content_row.经度,content_row.纬度,null,null,content_row.楼盘位置,0,var_system_date,int_content_id);
      end if;
      
      --attribute_value_tab表数据插入
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;--选择型,保存list ID
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.地区,getAttrId('地区',0,int_attrRef_cateId),int_content_id,getAreaListId(content_row.地区),0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.楼盘评测,getAttrId('楼盘评测',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.开发商,getAttrId('开发商',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;--需要计算价格区间并保存List ID
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.均价,getAttrId('均价',0,int_attrRef_cateId),int_content_id,calcPriceArea(content_row.均价),0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.楼盘位置,getAttrId('楼盘位置',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.户型区间,getAttrId('户型区间',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.楼盘形态,getAttrId('楼盘形态',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.区域,getAttrId('区域',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.开盘时间,getAttrId('开盘时间',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.交房时间,getAttrId('交房时间',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.占地面积,getAttrId('占地面积',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.建筑面积,getAttrId('建筑面积',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.户数,getAttrId('户数',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.幢数,getAttrId('幢数',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.楼层,getAttrId('楼层',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.绿化率,getAttrId('绿化率',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.容积率,getAttrId('容积率',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.停车位,getAttrId('停车位',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.产权年限,getAttrId('产权年限',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.装修情况,getAttrId('装修情况',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.销售情况,getAttrId('销售情况',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.交通状况,getAttrId('交通状况',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.竣工日期,getAttrId('竣工日期',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.最新资讯,getAttrId('最新资讯',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
      
      select ATTRIBUTE_VALUE_TAB_SEQ.nextval into int_attr_value_id from dual;
      Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.售楼电话,getAttrId('售楼电话',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);
    end loop;
    commit;
    dbms_output.put_line('导入完成');
    close temp_data_cur;
  end;
end;

select 'Insert into ATTRIBUTE_VALUE_TAB (INT_ATTRI_VALUE_ID,VAR_ATTRI_VALUE,INT_ATTRI_ID,INT_CONTENT_ID,INT_ATTR_LIST_ID,INT_DELETE_SIGN,DAT_MUDIFY_DATE) 
      values (int_attr_value_id,content_row.'||column_name||',getAttrId('''||column_name||''',0,int_attrRef_cateId),int_content_id,null,0,var_system_date);'
from user_tab_columns where table_name='APP_DATA_LOAD';

/*
  @author : Taylor
  @desc   : 根据地区获取地区对应的列表ID
  @param  : colname
              属性名称
  @param  : daymic
              是否从数据库动态获取属性ID
  @param  : cateId
              动态获取时所需要的分类ID
*/
create or replace
function getAttrId(colname in varchar2,daymic in char,cateId in number) return number
is
int_attr_id number;
begin
  if daymic=0
  then
    select int_attri_id into int_attr_id from attribute_tab where int_delete_sign=0 and var_attri_name=colname and int_cate_id=cateId;
    dbms_output.put_line(int_attr_id);
    return int_attr_id;
  else
    return 
      case colname
      when '地区' then 1187
      when '楼盘评测' then 1245
      when '开发商' then 1224
      when '均价' then 1231
      when '楼盘位置' then 1225
      when '户型区间' then 1230
      when '楼盘形态' then 1189
      when '区域' then 1244
      when '开盘时间' then 1235
      when '交房时间' then 1236
      when '占地面积' then 1226
      when '建筑面积' then 1227
      when '户数' then 1228
      when '幢数' then 1229
      when '楼层' then 1232
      when '绿化率' then 1233
      when '容积率' then 1234
      when '停车位' then 1237
      when '产权年限' then 1239
      when '装修情况' then 1240
      when '销售情况' then 1241
      when '交通情况' then 1242
      when '竣工日期' then 1243
      when '最新资讯' then 1238
      when '售楼电话' then 1118
    end;
  end if;
end getAttrId;

/*
  @author : Taylor
  @desc   : 根据地区获取地区对应的列表ID
  @param  : areaname
              地区名称
*/
create or replace function getAreaListId(areaname in varchar2) return number
is
begin
  return 
  case areaname
  when '德阳' then 1
  when '罗江' then 2
  when '广汉' then 3
  when '绵竹' then 4
  when '什邡' then 5
  when '中江' then 6
  end;
end getAreaListId;

/*
  @author : Taylor
  @desc   : 根据价格计算所在的价格区间ID集合
  @param  : price
              价格
*/
create or replace
function calcPriceArea(price in varchar2) return varchar2
is
temp varchar2(2000);
strnum varchar2(200);--从价格里截取的数字
list_ids varchar2(100);--价格区间ID集合
list_id varchar2(50);
split_char char := ',';--ID分隔符
begin
    temp := trim(regexp_replace(price,'[^0-9]',' '));--替换所有非字符串
    while lengthb(temp) > 0 loop
      strnum := regexp_substr(temp,'[0-9]+');--截取第一个非数字之前的字符串
      if strnum >= 1000 then --价格大于等于1000时才去处理
        list_id :=  
        case  
        when strnum < 3000 then 7
        when strnum >= 3000 and strnum < 4000 then 8
        when strnum >= 4000 and strnum < 5000 then 9
        when strnum >= 5000 then 10
        end;
        if list_ids is null or instr(list_ids,list_id,1,1) <= 0
        then
          list_ids := list_ids || split_char || list_id; --拼接价格区间ID,并剔除重复的
        end if;
      end if;
      temp := trim(substr(temp,lengthb(strnum)+1));
    end loop;
    list_ids := substr(list_ids,2);
    return list_ids;
end calcPriceArea;


/*
  @author : Taylor
  @desc   : 导入德阳房产属性,部分属性来自系统属性，此处不导入
  @param  : int_cate_id
              分类ID
*/
create or replace
procedure pro_attr_batch_imp(
int_cate_id in number--要添加到的分类ID
) is
int_attr_id number;--属性ID
begin
    select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'开发商',0,'2012-09-10 14:33:17',0,0);
    
    select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'楼盘位置',0,'2012-09-10 14:33:17',0,0);
    
    select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'占地面积',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'建筑面积',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'户数',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'幢数',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'户型区间',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'楼层',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'绿化率',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'容积率',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'开盘时间',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'交房时间',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'停车位',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'最新资讯',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'产权年限',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'装修情况',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'销售情况',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'交通状况',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'竣工日期',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'区域',0,'2012-09-10 14:33:17',0,0);
    
     select ATTRIBUTE_TAB_SEQ.nextval into int_attr_id from dual;
    Insert into ATTRIBUTE_TAB (INT_ATTRI_ID,INT_CATE_ID,VAR_ATTRI_NAME,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_INPUT_TYPE,INT_TYPE) values (int_attr_id,int_cate_id,'楼盘评测',0,'2012-09-13 14:23:39',0,0);
        
    commit;
end;

/*
  @author : Taylor
  @desc   : 字符串分割过程
  @param  : str
              要分割的字符串
*/
create or replace
PROCEDURE splitStr2Array(
    str IN VARCHAR2,int_content_id IN NUMBER)
AS
type array_str
IS
  TABLE OF VARCHAR2(100) INDEX BY binary_integer;
  myarray array_str;
  v_str VARCHAR2(4000) DEFAULT str;
  int_img_id number;--图片ID
  var_system_date varchar2(100);--系统时间
BEGIN
  select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') into var_system_date from dual;
  FOR i IN 0 .. LENGTH(str)-LENGTH(REPLACE(str,'|',''))+1
  LOOP
    IF instr(v_str,'|') = 0 THEN
      myarray(i+1)     := v_str ;
    ELSE
      myarray(i+1) := SUBSTR(v_str,0,instr(v_str,'|')-1);
    END IF;
    v_str := SUBSTR(v_str,instr(v_str,'|')+1);
  END LOOP;
  FOR i IN 0 ..length(str)-LENGTH(REPLACE(str,'|',''))
  LOOP
    select CONTENT_IMG_TAB_SEQ.nextval into int_img_id from dual;
    Insert into CONTENT_IMG_TAB (INT_IMG_ID,INT_CONTENT_ID,VAR_IMG_NAME,VAR_IMG_URL,INT_DELETE_SIGN,DAT_UPDATE_DATETIME,INT_TYPE) 
    values (int_img_id,int_content_id,null,'WORKDIR/ENTERPRISE_1020/1060/CATELOG/DETAIL/'||myarray(i+1),0,var_system_date,1);
    dbms_output.put_line(myarray(i+1));
  END LOOP ;
END splitStr2Array;